package com.vn.toaa.karaoke.database;

import java.util.ArrayList;
import java.util.List;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.vn.toaa.karaoke.ui.Constant;

public class SixNumberDatasource {
	
	private SQLiteDatabase mSqLiteDatabase;
	public static final String COL_ID = "Id";
	public static final String COL_LANGUAGE = "Language";
	public static final String COL_VOLUME = "Volume";
	public static final String COL_TITLE = "Title";
	public static final String COL_TITLE_SEARCH = "TitleSearch";
	public static final String COL_TITLE_ABBR = "TitleAbbreviation";
	public static final String COL_LYRIC = "Lyric";
	public static final String COL_LYRIC_SEARCH = "LyricSearch";
	public static final String COL_AUTHOR = "Author";
	public static final String COL_AUTHOR_SEARCH = "AuthorSearch";
	public static final String[] ALL_COLUMNS = {
		COL_ID,
		COL_LANGUAGE,
		COL_VOLUME,
		COL_TITLE,
		COL_TITLE_SEARCH,
		COL_TITLE_ABBR,
		COL_LYRIC,
		COL_LYRIC_SEARCH,
		COL_AUTHOR,
		COL_AUTHOR_SEARCH
	};
	
	public void open() {
		ExternalSQLiteHelper.getInstance().openDatabase();
		mSqLiteDatabase = ExternalSQLiteHelper.getInstance().getDatabase();
	}
	
	public void close() {
		ExternalSQLiteHelper.getInstance().closeDataBase();
	}
	
	public List<SongModel> selectAllSongs() {
		List<SongModel> result = new ArrayList<SongModel>();
		if (!mSqLiteDatabase.isOpen()) {
			open();
		}
		try {
			Cursor cursor = mSqLiteDatabase.query(Constant.TABLE_SIX_NUMBER, ALL_COLUMNS, null, null, null, null, null);
			if (cursor.getCount() > 0) {
				cursor.moveToFirst();
				while (!cursor.isAfterLast()) {
					result.add(cursorToModel(cursor));
					cursor.moveToNext();
				}
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public SongModel selectSongById(int id) {
		SongModel songModel = null;
		if (!mSqLiteDatabase.isOpen()) {
			open();
		}
		try {
			final String whereClause = String.format("%s = %d", COL_ID, id);
			Cursor cursor = mSqLiteDatabase.query(Constant.TABLE_SIX_NUMBER, ALL_COLUMNS, whereClause, null, null, null, null);
			if (cursor.getCount() > 0) {
				cursor.moveToFirst();
				songModel = cursorToModel(cursor);
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return songModel;
	}
	
	public List<SongModel> selectSongByLanguage(int language) {
		List<SongModel> result = new ArrayList<SongModel>();
		if (!mSqLiteDatabase.isOpen()) {
			open();
		}
		try {
			final String whereClause = String.format("%s = %d", COL_LANGUAGE, language);
			Cursor cursor = mSqLiteDatabase.query(Constant.TABLE_SIX_NUMBER, ALL_COLUMNS, whereClause, null, null, null, null);
			if (cursor.getCount() > 0) {
				cursor.moveToFirst();
				while (!cursor.isAfterLast()) {
					result.add(cursorToModel(cursor));
					cursor.moveToNext();
				}
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public List<SongModel> selectSongByVol(int vol) {
		List<SongModel> result = new ArrayList<SongModel>();
		if (!mSqLiteDatabase.isOpen()) {
			open();
		}
		try {
			final String whereClause = String.format("%s = %d", COL_VOLUME, vol);
			Cursor cursor = mSqLiteDatabase.query(Constant.TABLE_SIX_NUMBER, ALL_COLUMNS, whereClause, null, null, null, null);
			if (cursor.getCount() > 0) {
				cursor.moveToFirst();
				while (!cursor.isAfterLast()) {
					result.add(cursorToModel(cursor));
					cursor.moveToNext();
				}
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public List<SongModel> selectSongByTitle(String title) {
		List<SongModel> result = new ArrayList<SongModel>();
		if (!mSqLiteDatabase.isOpen()) {
			open();
		}
		try {
			final String whereClause = String.format("%s LIKE '%%s%'", COL_TITLE_SEARCH, title);
			Cursor cursor = mSqLiteDatabase.query(Constant.TABLE_SIX_NUMBER, ALL_COLUMNS, whereClause, null, null, null, null);
			if (cursor.getCount() > 0) {
				cursor.moveToFirst();
				while (!cursor.isAfterLast()) {
					result.add(cursorToModel(cursor));
					cursor.moveToNext();
				}
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public List<SongModel> selectSongByLyric(String lyric) {
		List<SongModel> result = new ArrayList<SongModel>();
		if (!mSqLiteDatabase.isOpen()) {
			open();
		}
		try {
			final String whereClause = String.format("%s LIKE '%%s%'", COL_LYRIC_SEARCH, lyric);
			Cursor cursor = mSqLiteDatabase.query(Constant.TABLE_SIX_NUMBER, ALL_COLUMNS, whereClause, null, null, null, null);
			if (cursor.getCount() > 0) {
				cursor.moveToFirst();
				while (!cursor.isAfterLast()) {
					result.add(cursorToModel(cursor));
					cursor.moveToNext();
				}
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public List<SongModel> selectSongByAuthor(String author) {
		List<SongModel> result = new ArrayList<SongModel>();
		if (!mSqLiteDatabase.isOpen()) {
			open();
		}
		try {
			final String whereClause = String.format("%s LIKE '%%s%'", COL_AUTHOR_SEARCH, author);
			Cursor cursor = mSqLiteDatabase.query(Constant.TABLE_SIX_NUMBER, ALL_COLUMNS, null, null, null, null, null);
			if (cursor.getCount() > 0) {
				cursor.moveToFirst();
				while (!cursor.isAfterLast()) {
					result.add(cursorToModel(cursor));
					cursor.moveToNext();
				}
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public List<SongModel> selectSongByTitleAndLyricAndAuthor(String title, String lyric, String author) {
		List<SongModel> result = new ArrayList<SongModel>();
		if (!mSqLiteDatabase.isOpen()) {
			open();
		}
		try {
			final String whereClause = String.format("%s LIKE '%%%s%%' OR %s LIKE '%%%s%%' OR %s LIKE '%%%s%%'",
					COL_TITLE_SEARCH, title, COL_LYRIC_SEARCH, lyric, COL_AUTHOR_SEARCH, author);
			Cursor cursor = mSqLiteDatabase.query(Constant.TABLE_SIX_NUMBER, ALL_COLUMNS, whereClause, null, null, null, null);
			if (cursor.getCount() > 0) {
				cursor.moveToFirst();
				while (!cursor.isAfterLast()) {
					result.add(cursorToModel(cursor));
					cursor.moveToNext();
				}
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	private SongModel cursorToModel(Cursor cursor) {
		SongModel songModel = new SongModel();
		
		songModel.setSongId(cursor.getInt(0));
		songModel.setLanguage(cursor.getInt(1));
		songModel.setVolume(cursor.getInt(2));
		songModel.setTitle(cursor.getString(3));
		songModel.setTitleSearch(cursor.getString(4));
		songModel.setTitleAbbr(cursor.getString(5));
		songModel.setLyric(cursor.getString(6));
		songModel.setLyricSearch(cursor.getString(7));
		songModel.setAuthor(cursor.getString(8));
		songModel.setAuthorSearch(cursor.getString(9));
		
		return songModel;
	}

}
